package com.wjwframework.jfinal.model;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.commons.lang3.StringUtils;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.ICallback;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.TableMapping;
import com.wjwframework.jfinal.vo.PageNavation;

/**
 * @className CommonDbUtil
 * @description 
 * @author wjw
 * @cTime 2016年5月6日下午4:13:52
 */
public class CommonDbUtil implements Serializable{

	private static final long serialVersionUID = -662818401550839182L;
	
	public String tableName;
	public String primaryKey[];
	
	@SuppressWarnings("rawtypes")
	public CommonDbUtil(Class<? extends Model> modelClass){
		this.tableName=TableMapping.me().getTable(modelClass).getName();			//表名
		this.primaryKey=TableMapping.me().getTable(modelClass).getPrimaryKey();		//主键包含组合主键
	}
	
	/**	计算数量
	 * @param condition
	 * @param values
	 * @return
	 */
	public Long count(String condition,Object... values){
		String sql="SELECT COUNT(*) FROM "+tableName;
		long result=0;
		if(StringUtils.isNotBlank(condition)){
			sql+=" WHERE "+condition;
		}
		if(values!=null&&values.length>0){
			result=Db.queryLong(sql, values);
		}else{
			result=Db.queryLong(sql);
		}
		return result;
	}

	
	/**
	 * 排序查找
	 * @param sortCondition
	 * @param whereCondition
	 * @param values
	 * @return
	 */
	public List<Record> findBySort(String sortCondition,String whereCondition,Object... values){
		String sql="SELECT * FROM "+tableName;
		List<Record> records=null;
		if(StringUtils.isNotBlank(whereCondition)){
			sql+=" WHERE "+whereCondition;
		}
		if(StringUtils.isNotBlank(sortCondition)){
			sql+=" ORDER BY "+sortCondition;
		}
		if(values!=null&&values.length>0){
			records=Db.find(sql,values);
		}else{
			records=Db.find(sql);
		}
		return records;
	}
	
	/**
	 * 更新表
	 * @param columnValueMap	列和值的集合
	 * @param condition	条件
	 * @param conditonVal	条件值
	 * @return
	 */
	public int update(Map<String,Object> columnValueMap,String condition,Object... conditonVal){
		String tempSql="UPDATE "+tableName+" SET ";
		Set<Entry<String, Object>> set=columnValueMap.entrySet();
		Iterator<Entry<String, Object>> iterator=set.iterator();
		int len=columnValueMap.size();
		int currentIndex=0;
		final List<Object> values=new ArrayList<Object>();
		while(iterator.hasNext()){
			Entry<String,Object> entry=iterator.next();
			String columnName=entry.getKey();
			Object columnValue=entry.getValue();
			values.add(columnValue);
			tempSql+=columnName+"=?";
			currentIndex++;
			if(currentIndex!=len){
				tempSql+=",";
			}
		}
		if(condition!=null&&!condition.trim().equals("")){
			tempSql+=" WHERE "+condition;
		}
		if(conditonVal!=null&&conditonVal.length>0){
			for(Object val:conditonVal){
				values.add(val);
			}
		}
		final String sql=tempSql;
		Object result=Db.execute(new ICallback() {
			@Override
			public Object call(Connection conn) throws SQLException {
				PreparedStatement ps=conn.prepareStatement(sql);
				for(int i=1,len=values.size();i<=len;i++){
					ps.setObject(i,values.get(i-1));
				}
				return ps.executeUpdate();
			}
		});
		return Integer.parseInt(""+result);
	}
	
	/**
	 * 更新表;只需要传入SET后面的东东及条件即可
	 * @param colVal
	 * @param condition
	 * @param conditionVal
	 * @return
	 */
	public int updateSet(String colVal,String condition,Object... conditionVal){
		String sql="UPDATE "+tableName+" SET "+colVal;
		int result=0;
		if(StringUtils.isNotBlank(condition)){
			sql+=" WHERE "+condition;
			result=Db.update(sql,conditionVal);
		}else{
			result=Db.update(sql);
		}
		return result;
	}
	
	/**
	 * 删除(delte操作),愖用
	 * @param condition
	 * @param values
	 * @return
	 */
	public boolean delete(String condition,Object... values){
		String sql="DELETE FROM "+tableName;
		int result=0;
		if(StringUtils.isNotBlank(condition)){
			sql+=" WHERE "+condition;
			result=Db.update(sql, values);
		}else{
			result=Db.update(sql);
		}
		return result>0;
	}
	
	
	/**
	 * 查找所有并根据sortCondition排序
	 * @param sortCondition排序条件(例:id DESC)
	 * @return
	 */
	public List<Record> findAllSort(String sortCondition){
		String sql="SELECT * FROM "+tableName;
		List<Record> records=null;
		if(StringUtils.isNotBlank(sortCondition)){
			sql+=" ORDER BY "+sortCondition;
			records=Db.find(sql);
		}else{
			records=Db.find(sql);
		}
		return records;
	}
	
	/**
	 * 根据条件查找
	 * @param condition
	 * @param values
	 * @return
	 */
	public Record findFirst(String condition,Object... values){
		String sql="SELECT * FROM "+tableName;
		Record record=null;
		if(StringUtils.isNotBlank(condition)){
			sql+=" WHERE "+condition;
			record=Db.findFirst(sql, values);
		}else{
			record=Db.findFirst(sql);
		}
		return record;
	}
	
	/**
	 * 取出指定的列
	 * @param id
	 * @param columnNames
	 * @return
	 */
	public Record findFirstById(Object ids[],String columnNames){
		StringBuilder sql=new StringBuilder("SELECT "+columnNames+" FROM "+tableName +" WHERE ");
		//mysql
    	for(int i = 0; i < primaryKey.length; i++){
    		if(i > 0)
    			sql.append(" and ");
    		sql.append("`").append(primaryKey[i]).append("` = ?");
    	}
    	return Db.findFirst(sql.toString(),ids);
	}
	
	/**
	 * 对指定列求和
	 * @param column
	 * @param condition
	 * @param values
	 * @return
	 */
	public BigDecimal sum(String column,String condition,Object... values){
		String sql="SELECT SUM("+column+") FROM "+tableName;
		BigDecimal result=new BigDecimal(0);
		if(StringUtils.isNotBlank(condition)){
			sql+=" WHERE "+condition;
			result=Db.queryBigDecimal(sql, values);
		}else{
			result=Db.queryBigDecimal(sql);
		}
		return result;
	}
	
	/**
	 * 根据条件查找指定列
	 * @param columnNames
	 * @param condition
	 * @param values
	 * @return
	 */
	public List<Record> findColumns(String columnNames,String condition,Object... values){
		List<Record> records=null;
		String sql="SELECT "+columnNames+" FROM "+tableName;
		if(StringUtils.isNotBlank(condition)){
			sql+=" WHERE "+condition;
		}
		if(values!=null&&values.length>0){
			records=Db.find(sql,values);				
		}else{
			records=Db.find(sql);				
		}
		return records;
	}
	
	/**
	 * 从record中获取指定的列
	 * @param record
	 * @param columnNames
	 * @return
	 */
	public Record getColumns(Record record,String columnNames){
		Record result=new Record();
		if(StringUtils.isNotBlank(columnNames)){
			String[] columnNameArr=StringUtils.split(columnNames,",");
			for(String columnName:columnNameArr){
				result.set(columnName,record.get(columnName));
			}
			return result;
		}else{
			return record;
		}
	}
	
	/**
	 * 获取匹配sql
	 * @param search搜索词,columns匹配列
	 * @param none true代表where false AND
	 * @return
	 */
	public String getLikeSql(String search,Boolean none,String... columns){
		String sql=" ";
		//拼接模糊查询条件
		if(StringUtils.isNotBlank(search)){
			sql+=none?"WHERE (":"AND (";
			for (int i=0; i<columns.length; i++) {
				sql+=columns[i]+" LIKE '%"+search+"%' OR " ;
			}
			sql=sql.substring(0, sql.lastIndexOf("OR"))+")";
		}
		return sql;
	}

	/**
	 * 获取匹配和排序sql
	 * @param page,columns搜索列
	 * @return
	 */
	public String getLikeOrSortSql(PageNavation page,Boolean none,String... columns){
		String sql=" ";
		//拼接模糊查询条件
		if(StringUtils.isNotBlank(page.getSearch())){
			sql+=none?"WHERE (":"AND (";
			for (int i=0; i<columns.length; i++) {
				sql+=columns[i]+" LIKE '%"+page.getSearch()+"%' OR " ;
			}
			sql=sql.substring(0, sql.lastIndexOf("OR"))+")";
		}
		//拼接排序参数
		if(StringUtils.isNotBlank( page.getSort() )){
			sql+="ORDER BY "+page.getSort()+" ";
			//拼接排序方式
			if( StringUtils.isNotBlank( page.getOrder()) ){
				sql+=page.getOrder();
			}
		}
		return sql;
	}
	
	/**
	 * 获取排序sql
	 * @param page
	 * @return
	 */
	public String getSortSql(PageNavation page){
		String sql="";
		//拼接排序参数
		if(StringUtils.isNotBlank(page.getSort())){
			sql+="ORDER BY "+page.getSort()+" ";
			//拼接排序方式
			if(StringUtils.isNotBlank(page.getOrder())){
				sql+=page.getOrder();
			}
		}
		return sql;
	}
	
}
